﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using BookStore.Common;
using System.Data.SqlClient;
using BookStore.Models;
using System.Data;

namespace BookStore.Controllers
{
    public class ReceiptDetailController : Controller
    {
        private static ReceiptDetailController _controller = null;
        public static ReceiptDetailController getController()
        {
            if (_controller == null)
                return new ReceiptDetailController();
            else
                return _controller;
        }
        public ReceiptDetailController()
        {
            _controller = this;
        }

        public void InsertReceiptDetail(ReceiptDetail receipt)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_InsertReceiptDetail", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ReceiptID",receipt.Fk_ReceiptID);
                cmd.Parameters.AddWithValue("@ProductID", receipt.Fk_ProductID);
                cmd.Parameters.AddWithValue("@StoringQuantity", receipt.StoringQuantity);
                cmd.Parameters.AddWithValue("@AmountOfReceipt", receipt.AmountOfReceipt);
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        public void SetQuantityReceiptDetail(ReceiptDetail receipt)
        {
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand("BS_SetQuantityReceiptDetail", con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@ReceiptID", receipt.Fk_ReceiptID);
                cmd.Parameters.AddWithValue("@ProductID", receipt.Fk_ProductID);
                cmd.Parameters.AddWithValue("@StoringQuantity", receipt.StoringQuantity);
                cmd.Parameters.AddWithValue("@AmountOfReceipt", receipt.AmountOfReceipt);
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }

        internal int getReceiptDetailID(String ProductName, String ReceiptID)
        {
            DataTable dt = new DataTable("GetReceiptDetailID");
            con = BSCommon.getConnection();
            int temp = 0;
            try
            {
                con.Open();
                SqlCommand cmd = new SqlCommand("BS_GetReceiptDetailID", con);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.Add("@ProductName", SqlDbType.NVarChar).Value = ProductName;
                cmd.Parameters.Add("@ReceiptID", SqlDbType.Int).Value = ReceiptID;
                SqlDataReader sdr = cmd.ExecuteReader();
                dt.Load(sdr);
                temp = Convert.ToInt32(dt.Rows[0][0].ToString());
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return temp;
        }

        internal DataTable getReceiptDetailByID(int ReceiptID)
        {
            DataTable dt = new DataTable("ReceiptDetail");
            string sql = @"SELECT  dbo.PRODUCT.ProductName, dbo.PRODUCT.StoringPrice, dbo.RECEIPTDETAIL.StoringQuantity, dbo.RECEIPTDETAIL.AmountOfReceipt
                                    FROM   dbo.PRODUCT INNER JOIN
                                             dbo.RECEIPTDETAIL ON dbo.PRODUCT.ProductID = dbo.RECEIPTDETAIL.ProductID INNER JOIN
                                             dbo.RECEIPT ON dbo.RECEIPTDETAIL.ReceiptID = dbo.RECEIPT.ReceiptID
                                    WHERE    dbo.ReceiptDetail.ReceiptID = " + ReceiptID;
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                cmd = new SqlCommand(sql, con);
                cmd.CommandType = CommandType.Text;
                var result = cmd.ExecuteReader();
                dt.Load(result);
            }
            catch (Exception e)
            {
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
            return dt;
        }

        internal void removeReceiptDetailByID(int ReceiptDetailID)
        {
            string sql = @"Delete from ReceiptDetail where ReceiptDetailID = " + ReceiptDetailID;
            con = BSCommon.getConnection();
            try
            {
                con.Open();
                tran = con.BeginTransaction();
                cmd = new SqlCommand(sql, con);
                cmd.Transaction = tran;
                cmd.CommandType = CommandType.Text;
                cmd.ExecuteNonQuery();
                tran.Commit();
            }
            catch (Exception e)
            {
                tran.Rollback();
                throw e;
            }
            finally
            {
                con.Dispose();
                con.Close();
            }
        }
    }
}
